{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "intro",
   "metadata": {},
   "source": [
    "# 为什么要进行数据清洗\n",
    "\n",
    "## 核心原因\n",
    "\n",
    "**\"Garbage In, Garbage Out\"** - 脏数据导致错误结论\n",
    "\n",
    "- **时间占比**：数据科学家60%-80%的时间用于数据清洗\n",
    "- **质量影响**：脏数据直接影响分析结果和商业决策\n",
    "- **成本代价**：数据质量问题可能导致重大经济损失\n",
    "\n",
    "---\n",
    "\n",
    "## 数据为什么会\"脏\"？\n",
    "\n",
    "| 来源 | 具体问题 | 示例 |\n",
    "|------|----------|------|\n",
    "| **人为因素** | 录入错误、格式不一致 | 电话号码输错、姓名多空格、日期格式混乱 |\n",
    "| **系统因素** | 多系统整合、编码不同 | SAP与CRM系统数据格式不一致 |\n",
    "| **环境因素** | 传感器故障、网络中断 | 温度异常、数据丢失、记录中断 |\n",
    "\n",
    "---\n",
    "\n",
    "## 脏数据的代价\n",
    "\n",
    "### 真实案例\n",
    "\n",
    "1. **零售业**：重复记录 → 错误库存预测 → 缺货/积压\n",
    "2. **金融业**：异常值未处理 → 风险评估偏差 → 错误贷款决策\n",
    "3. **制造业**：传感器数据噪声 → 维护系统失效 → 生产线停产\n",
    "\n",
    "---\n",
    "\n",
    "## 常见数据质量问题\n",
    "\n",
    "| 问题类型 | 影响 | 检测方法 |\n",
    "|----------|------|----------|\n",
    "| **缺失值** | 影响统计结果 | `isnull().sum()` |\n",
    "| **异常值** | 导致模型偏差 | 箱线图、IQR |\n",
    "| **重复值** | 数据失真 | `duplicated().sum()` |\n",
    "| **格式不一致** | 无法合并分析 | 正则表达式检查 |\n",
    "| **单位不统一** | 计算错误 | 标准化检查 |\n",
    "| **数据类型错误** | 程序报错 | `dtypes` 检查 |\n",
    "\n",
    "---\n",
    "\n",
    "## 数据清洗的技能要求\n",
    "\n",
    "```\n",
    "数据清洗 = 专业知识 + 统计思维 + 工程能力\n",
    "    │           │            │           │\n",
    "    │           │            │           └─ 高效处理大规模数据\n",
    "    │           │            └─ 识别异常值、处理缺失值\n",
    "    │           └─ 理解业务规则和行业特点\n",
    "    └─ 艺术与科学的结合\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "## 未来趋势\n",
    "\n",
    "- **自动化清洗**：机器学习算法自动检测和修正数据问题\n",
    "- **实时清洗**：流式数据处理,在线交易/物联网场景\n",
    "- **协同清洗**：多部门共同维护数据质量标准\n",
    "\n",
    "---\n",
    "\n",
    "## 实践：创建脏数据集\n",
    "\n",
    "下面的代码将创建一个包含各种数据质量问题的数据集,用于后续清洗练习。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "create-messy-data",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 数据集中包含的问题类型 ===\n",
      "\n",
      "1. 基础数据问题：日期/姓名/年龄/工资格式不统一\n",
      "2. 联系方式问题：邮箱/手机号/地址格式错误\n",
      "3. 组织数据问题：部门/产品编码命名不一致\n",
      "4. 统计异常问题：订单金额/评分/时效/评价数据异常\n",
      "5. 其他问题：重复数据、特殊值、系统错误\n",
      "\n",
      "=== 数据预览 ===\n",
      "           日期  姓名  年龄    工资                   邮箱          手机号  \\\n",
      "0  2023-03-24  王五  20  9422   user82@example.com  17740017658   \n",
      "1  2024-02-24  钱七  51  5130  user419@example.com  13593142603   \n",
      "2  2023-12-27  张三  51  7708  user360@example.com  15219838150   \n",
      "3  2024-12-07  吴十  29  9569  user706@example.com  18369903793   \n",
      "4  2025-03-18  赵六  41  9473  user807@example.com  18262486457   \n",
      "\n",
      "                 地址   部门     产品编码     订单金额  客户评分  物流时效 评价数量  \n",
      "0  beijing chaoyang  销售部  PRD3279  6201.96  86.8   4.9   93  \n",
      "1      北京市朝阳区建国路20号  市场部  PRD9243  2565.66  82.7  18.1  106  \n",
      "2      北京市朝阳区建国路20号  技术部  PRD3034   5685.1  81.4  38.2  111  \n",
      "3      北京市朝阳区建国路40号  市场部  PRD4491  2607.32  89.3  18.2  111  \n",
      "4      北京市朝阳区建国路60号  人事部  PRD8690  3173.57  92.7  25.7  114  \n",
      "\n",
      "=== 数据信息 ===\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1020 entries, 0 to 1019\n",
      "Data columns (total 13 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   日期      1020 non-null   object\n",
      " 1   姓名      1020 non-null   object\n",
      " 2   年龄      1018 non-null   object\n",
      " 3   工资      1018 non-null   object\n",
      " 4   邮箱      1020 non-null   object\n",
      " 5   手机号     1018 non-null   object\n",
      " 6   地址      1018 non-null   object\n",
      " 7   部门      1020 non-null   object\n",
      " 8   产品编码    1018 non-null   object\n",
      " 9   订单金额    1019 non-null   object\n",
      " 10  客户评分    1019 non-null   object\n",
      " 11  物流时效    1019 non-null   object\n",
      " 12  评价数量    1018 non-null   object\n",
      "dtypes: object(13)\n",
      "memory usage: 103.7+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime, timedelta\n",
    "import random\n",
    "import os\n",
    "\n",
    "def create_messy_data():\n",
    "    \"\"\"创建包含各种数据问题的综合数据集\"\"\"\n",
    "    \n",
    "    # 设置随机种子,确保可重复性\n",
    "    np.random.seed(42)\n",
    "    n_rows = 1000\n",
    "    \n",
    "    # ============== 1. 基础信息数据 ==============\n",
    "    # 1.1 日期数据（格式不统一）\n",
    "    dates = [(datetime(2023, 1, 1) + timedelta(days=x)).strftime('%Y-%m-%d') for x in range(n_rows)]\n",
    "    dates[10:20] = ['2023/01/15', '01-15-2023', '2023.01.16', '20230117', 'invalid_date',\n",
    "                    '2023-13-01', '2023-01-32', '01/19/2023', '2023-01', '2023']\n",
    "\n",
    "    # 1.2 姓名（包含格式问题）\n",
    "    names = ['张三', '李四', '王五', '赵六', '钱七', '孙八', '周九', '吴十']\n",
    "    full_names = [random.choice(names) for _ in range(n_rows)]\n",
    "    full_names[30:40] = [' 张三', '李四 ', ' 王五 ', 'zhang san', 'li si', '王Wu', \n",
    "                         'ZHANG SAN', '李   四', '王\\t五', '赵\\n六']\n",
    "\n",
    "    # 1.3 年龄（包含异常值）\n",
    "    ages = list(np.random.randint(20, 60, n_rows))\n",
    "    ages[40:50] = [-1, 0, 150, 200, None, None, 999, 25, 30, 'invalid_age']\n",
    "\n",
    "    # 1.4 工资（包含格式问题）\n",
    "    salaries = list(np.random.randint(5000, 20000, n_rows))\n",
    "    salaries[50:60] = [-5000, '1,000,000', '5,000', 'N/A', None, None,\n",
    "                       '8888.88', '12345.678', 'invalid_salary', '10000+']\n",
    "\n",
    "    # ============== 2. 联系方式数据 ==============\n",
    "    # 2.1 邮箱（包含无效格式）\n",
    "    emails = [f'user{i}@example.com' for i in range(n_rows)]\n",
    "    emails[60:70] = ['invalid_email', 'user@.com', '@example.com', 'user@', \n",
    "                     'user@example..com', 'user@com', ' user@example.com ',\n",
    "                     'user@example.com.', '.user@example.com', 'user.@example.com']\n",
    "\n",
    "    # 2.2 手机号（包含格式问题）\n",
    "    phones = [f'1{random.choice([\"3\", \"5\", \"7\", \"8\", \"9\"])}{random.randint(100000000, 999999999)}' \n",
    "              for _ in range(n_rows)]\n",
    "    phones[70:80] = ['123', '12345678901234', '2345678901', 'abc12345678',\n",
    "                     '13-888-88888', '138 8888 8888', None, None, \n",
    "                     '+86-13888888888', '086-13888888888']\n",
    "\n",
    "    # 2.3 地址（包含格式问题）\n",
    "    addresses = [f'北京市朝阳区建国路{random.randint(1, 100)}号' for _ in range(n_rows)]\n",
    "    addresses[80:90] = [' 北京市朝阳区建国路1号 ', '北京市 朝阳区 建国路2号',\n",
    "                        'beijing chaoyang', None, None, '', \n",
    "                        '北京市朝阳区建国路1号\\n', '\\t北京市朝阳区建国路2号',\n",
    "                        '   北京市朝阳区建国路3号   ', '北京市朝阳区建国路1号']\n",
    "\n",
    "    # ============== 3. 组织数据 ==============\n",
    "    # 3.1 部门（名称不统一）\n",
    "    departments = list(np.random.choice(['销售部', '技术部', '市场部', '人事部'], n_rows))\n",
    "    departments[90:100] = ['Sales', 'Tech', 'Marketing', 'HR', \n",
    "                          '销售部门', '技术部门', '市场部门', '人事部门',\n",
    "                          'sales', 'SALES']\n",
    "\n",
    "    # 3.2 产品编码（格式不统一）\n",
    "    product_codes = [f'PRD{random.randint(1000, 9999)}' for _ in range(n_rows)]\n",
    "    product_codes[100:110] = ['prd1234', 'PRD-1234', 'PRD_1234', 'prd_1234',\n",
    "                             'Prd1234', 'PRD1234 ', ' PRD1234', None, None, '']\n",
    "\n",
    "    # ============== 4. 统计数据 ==============\n",
    "    # 4.1 订单金额（需要统计经验判断）\n",
    "    order_amounts = list(np.random.lognormal(mean=8, sigma=0.6, size=n_rows))\n",
    "    order_amounts = [round(x, 2) for x in order_amounts]\n",
    "    order_amounts[200:210] = [0.01, 1000000, -50, 500.999, 'pending', \n",
    "                              '1,234.56', '待审核', None, float('inf'), 'error']\n",
    "\n",
    "    # 4.2 客户评分（需要业务经验判断）\n",
    "    customer_scores = list(np.random.normal(loc=85, scale=10, size=n_rows))\n",
    "    customer_scores = [min(100, max(0, round(x, 1))) for x in customer_scores]\n",
    "    customer_scores[210:220] = [150, -10, 'A+', '100分', 'Excellent', \n",
    "                                '待评估', None, '未评分', '95~100', 'N/A']\n",
    "\n",
    "    # 4.3 物流时效（需要统计经验判断）\n",
    "    delivery_times = list(np.random.gamma(shape=2, scale=10, size=n_rows))\n",
    "    delivery_times = [round(x, 1) for x in delivery_times]\n",
    "    delivery_times[220:230] = [0.1, 240, -2, '2-3天', '已签收', \n",
    "                               None, 'delayed', '48h+', '待确认', 0]\n",
    "\n",
    "    # 4.4 评价数量（需要文本分析经验）\n",
    "    review_counts = list(np.random.poisson(lam=100, size=n_rows))\n",
    "    review_counts[230:240] = [-1, 999999, 'hidden', '1000+', None, \n",
    "                              '待统计', 'restricted', '有争议', float('nan'), '未知']\n",
    "\n",
    "    # ============== 创建DataFrame ==============\n",
    "    df = pd.DataFrame({\n",
    "        '日期': dates,\n",
    "        '姓名': full_names,\n",
    "        '年龄': ages,\n",
    "        '工资': salaries,\n",
    "        '邮箱': emails,\n",
    "        '手机号': phones,\n",
    "        '地址': addresses,\n",
    "        '部门': departments,\n",
    "        '产品编码': product_codes,\n",
    "        '订单金额': order_amounts,\n",
    "        '客户评分': customer_scores,\n",
    "        '物流时效': delivery_times,\n",
    "        '评价数量': review_counts\n",
    "    })\n",
    "\n",
    "    # 随机打乱一些行的顺序\n",
    "    df = df.sample(frac=1).reset_index(drop=True)\n",
    "\n",
    "    # 添加一些重复行\n",
    "    df = pd.concat([df, df.iloc[0:20]], ignore_index=True)\n",
    "\n",
    "    # 确保输出目录存在（使用正确的相对路径）\n",
    "    if not os.path.exists('../data'):\n",
    "        os.makedirs('../data')\n",
    "\n",
    "    # 保存到CSV文件\n",
    "    df.to_csv('../data/messy_data.csv', index=False, encoding='utf-8')\n",
    "    \n",
    "    return df\n",
    "\n",
    "# 创建脏数据集\n",
    "df = create_messy_data()\n",
    "\n",
    "print(\"=== 数据集中包含的问题类型 ===\")\n",
    "print(\"\\n1. 基础数据问题：日期/姓名/年龄/工资格式不统一\")\n",
    "print(\"2. 联系方式问题：邮箱/手机号/地址格式错误\")\n",
    "print(\"3. 组织数据问题：部门/产品编码命名不一致\")\n",
    "print(\"4. 统计异常问题：订单金额/评分/时效/评价数据异常\")\n",
    "print(\"5. 其他问题：重复数据、特殊值、系统错误\\n\")\n",
    "\n",
    "print(\"=== 数据预览 ===\")\n",
    "print(df.head())\n",
    "print(\"\\n=== 数据信息 ===\")\n",
    "print(df.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "check-problems",
   "metadata": {},
   "source": [
    "## 快速检查数据质量\n",
    "\n",
    "### 常用检查方法\n",
    "\n",
    "| 检查项 | 代码 | 说明 |\n",
    "|--------|------|------|\n",
    "| 缺失值 | `df.isnull().sum()` | 统计每列缺失值数量 |\n",
    "| 重复值 | `df.duplicated().sum()` | 统计重复行数量 |\n",
    "| 数据类型 | `df.dtypes` | 检查列的数据类型 |\n",
    "| 唯一值 | `df['col'].nunique()` | 检查列的唯一值数量 |\n",
    "| 值分布 | `df['col'].value_counts()` | 查看值的分布情况 |\n",
    "| 统计信息 | `df.describe()` | 数值列的统计摘要 |\n",
    "\n",
    "---\n",
    "\n",
    "## 小结\n",
    "\n",
    "**核心观点**：\n",
    "1. 数据清洗是数据分析的基础,占据60%-80%的工作时间\n",
    "2. 脏数据来自人为、系统、环境三大因素\n",
    "3. 数据质量问题可能导致严重的商业损失\n",
    "4. 数据清洗需要专业知识、统计思维和工程能力\n",
    "\n",
    "**下一步**：学习具体的数据清洗技术和方法"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
